package com.howard.tools.file;

import java.io.File;

import org.apache.commons.io.FileUtils;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class JacobExcelHelper {
	
	
	public static void readExcel(){
		String folderStr = "G:/tmp/input/安徽";
		
		ComThread.InitSTA();
        ActiveXComponent xl = new ActiveXComponent("Excel.Application");
        Dispatch.put(xl, "Visible", new Variant(false));
        Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
        File folder = new File(folderStr);
        for(File file: folder.listFiles()){
            Dispatch workbook = Dispatch.call(workbooks, "Open", file.getAbsolutePath()).toDispatch();
            Dispatch  sheets = Dispatch.get(workbook,"Sheets").toDispatch();
            Dispatch sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get,  
          	      new Object[] { new String("商品现存量查询") }, new int[1]).toDispatch();
            Dispatch cell = Dispatch.invoke(sheet,"Range",Dispatch.Get,new Object[] {"A1"},new int[1]).toDispatch();
			String value = Dispatch.get(cell, "Value").toString();
			String sheetName = Dispatch.get(sheet, "Name").toString();
			System.out.println("value=" + value);
            
            Dispatch usedRange = Dispatch.get(sheet,"UsedRange").toDispatch();
            Dispatch usedRows = Dispatch.get(usedRange,"Rows").toDispatch();
            String rowCount = Dispatch.get(usedRows,"Count").toString();
            System.out.println("rowCount="+rowCount);
        }
        xl.invoke("Quit", new Variant[] {});
        ComThread.Release();
            
	}
	
	public static void saveAs(){
		ActiveXComponent xl = new ActiveXComponent("Excel.Application");
		Dispatch.put(xl, "Visible", new Variant(false));
        try
        {
//            Dispatch.put(xl, "Visible", new Variant(false));
//            Dispatch.put(xl, "DisplayAlerts", new Variant(false));
            Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
            Dispatch workbook = Dispatch.call(workbooks, "Open", "G:\\tmp\\input\\anhui\\ccc.xls").toDispatch();
            /*
             * xls:-4143
             * xlsx:51
             * xlsm:52
             * xlsb:50
             * xls:56
             */
            Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] { "G:\\tmp\\test.xls", new Variant(-4143) }, new int[1]);
            
            Dispatch.call(workbook, "Close", new Variant(false));
        }
        catch (Exception e)
        {
        	e.printStackTrace();
        }
        finally
        {
            xl.invoke("Quit", new Variant[]{});
            xl = null;
        }
	}
	
	public static void converFiles(File inFolder, File outFolder){
		ActiveXComponent xl = new ActiveXComponent("Excel.Application");
		Dispatch.put(xl, "Visible", new Variant(false));
        Dispatch.put(xl, "DisplayAlerts", new Variant(false));
        
        try{
        	if(outFolder.exists()){
        		FileUtils.deleteDirectory(outFolder);   
        	}
        	outFolder.mkdirs();
        	
            Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
            
            for(File excelInFolder : inFolder.listFiles()){
            	if(excelInFolder.isFile()){
            		continue;
            	}
            	File excelOutFolder = new File(outFolder.getAbsolutePath()+File.separator+excelInFolder.getName());
            	if(excelOutFolder.exists() == false){
            		excelOutFolder.mkdirs();
            	}
            	for(File excelFile : excelInFolder.listFiles()){
            		String inFileStr = excelFile.getAbsolutePath();
            		String outFileStr = excelOutFolder.getAbsolutePath()+File.separator+excelFile.getName();
            		/*
	                 * xls:-4143
	                 * xlsx:51
	                 * xlsm:52
	                 * xlsb:50
	                 * xls:56
	                 */
	            	int excelFormat = 51;
	            	if(excelFile.getName().toLowerCase().endsWith(".xls")){
	            		excelFormat = -4143;
	            	}
	            	
//            		inFileStr = inFileStr.replaceAll("\\\\", "\\\\\\\\");
//            		outFileStr = outFileStr.replaceAll("\\\\", "\\\\\\\\");
            		System.out.println("正在格式化:"+inFileStr);
//	            	System.out.println("inFileStr="+inFileStr);
//	            	System.out.println("outFileStr="+outFileStr);
//	            	System.out.println("excelFormat="+excelFormat);
            		
	            	Dispatch workbook = Dispatch.call(workbooks, "Open", excelFile.getAbsolutePath()).toDispatch();
	            	Dispatch.call(workbook, "SaveAs", new Variant(outFileStr),new Variant("1"));
	                Dispatch.call(workbook, "Close", new Variant(false));
            	}
            }
        }
        catch (Exception e){
        	e.printStackTrace();
        }
        finally {
            xl.invoke("Quit", new Variant[]{});
            xl = null;
        }
        System.out.println();
	}
	
	public static void main(String[] args) {  
        try
        {
        	long start = System.currentTimeMillis();
        	converFiles(new File("G:\\tmp\\input"), new File("G:\\tmp\\input_converted"));
        	
        	System.out.println("结束, 花费时间(毫秒):"+(System.currentTimeMillis()-start));
        }
        catch (Exception e)
        {
        	e.printStackTrace();
        }
    }  
}
